package attend.dao.holidays;

import org.apache.log4j.Logger;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;

import attend.com.CodeMetrix;

import exception.BaseException;
import util.DBConnection;

public class HolidaysDao extends DBConnection {
	/**
	 * Logger for this class
	 */
	private static final Logger logger = Logger.getLogger(HolidaysDao.class);
	
	private Connection 			con			=			null;
	
	private PreparedStatement 	pstmt		=			null;
	
	private ResultSet			rs			=			null;
	
	private	StringBuffer		sql 		= 			new StringBuffer();
	
	private List<HashMap<String, String>>	list;

	private String msg;

	private Object crud;

	private int rowCount;
	
	public HolidaysDao() {}
	
	
	/**
	 * <pre>
	 * 휴일등록 - 입력된 년도를 조회한다.
	 * 휴일테이블에 데이터가 없을시 현재년도를 넣는다.
	 * </pre>
	 * @throws BaseException
	 * @author Yang D
	 * @since	2009/02/12
	 */
	public void retrieveYearList() throws BaseException {

		try {

			con = getDataSource().getConnection(); //근태관리 Connection

			sql.setLength(0);

			sql.append("                SELECT                                                    \r\n");
			sql.append("                        Z.YEAR  YEAR_CD                                   \r\n");
			sql.append("                    ,   Z.YEAR  YEAR_NM                                   \r\n");
			sql.append("                FROM                                                      \r\n");
			sql.append("                    (SELECT YEAR(GETDATE()) AS 'YEAR') Z                  \r\n");
			sql.append("                UNION                                                     \r\n");
			sql.append("                SELECT  YEAR(HOLIDAY_DT) AS 'YEAR'                        \r\n");
			sql.append("                    ,   YEAR(HOLIDAY_DT) AS 'YEAR'                        \r\n");
			sql.append("                FROM    TB_HOLIDAYS                                       \r\n");

			System.out.println("======================================\r\n");
			System.out.println("년도\r\n" + sql.toString());
			System.out.println("======================================\r\n");

			pstmt = con.prepareStatement(sql.toString());
			rs = pstmt.executeQuery();

			list = new ArrayList<HashMap<String, String>>();

			while (rs.next()) {
				//				각 행의 각각컬럼의 데이터를 담는다.
				HashMap<String, String> hash_map = new HashMap<String, String>();

				//hash_map.put("대소문자로 구분하여 사용할 것." , "디비에서는 상관없음.");
				//jsp에서 <s:property value='upper_dept_no'/> 와 같아야 함.
				hash_map.put("year", rs.getString("YEAR_CD")); //연도 key
				hash_map.put("yyyy", rs.getString("YEAR_NM")); //연도 value

				list.add(hash_map); // 한행의 데이터인 맵객체를 담는다.
			}

		} catch (SQLException se) {
			se.printStackTrace();
			BaseException dex = new BaseException();
			dex.setException(se);
			dex.setAppReturnCode("10");
			throw dex;
		} catch (Exception e) {
			BaseException dex = new BaseException();
			dex.setException(e);
			dex.setAppReturnCode("20");
			throw dex;
		} finally {
			try {
				if (pstmt != null)
					pstmt.close();
				if (rs != null)
					rs.close();
				if (con != null)
					con.close();
			} catch (SQLException fse) {
				BaseException dex = new BaseException();
				dex.setException(fse);
				dex.setAppReturnCode("10");
				throw dex;
			}
		}
	}
	
	/**
	 * <pre>조회수를 구한다.</pre>
	 * @param year
	 * @param month
	 * @throws BaseException
	 * @since	2009/02/12
	 */
	public void retrieveHolidaysRowCount(String year , String month) throws BaseException {
		
		try {

			con = getDataSource().getConnection(); //근태관리 Connection

			sql.setLength(0);

            sql.append("            SELECT  COUNT(*)         ROWCNT                                                   \r\n");
            sql.append("            FROM                                                                           		\r\n");
            sql.append("            (                                                                              		\r\n");
            sql.append("            	SELECT  ROW_NUMBER() OVER(ORDER BY HOLIDAY_DT) ROWNUM                      		\r\n");
            sql.append("            		,   H.HOLIDAYS_SEQ                                                     		\r\n");
            sql.append("            		,   CONVERT(VARCHAR , H.HOLIDAY_DT , 112)       DATE                   		\r\n");
            sql.append("            		,   H.HOLIDAY_TP                                TYPE_CD                		\r\n");
            sql.append("            		,   C.CD_NM                                     TYPE_NM                		\r\n");
            sql.append("            		,   H.HOLIDAY_NM                                                       		\r\n");
            sql.append("            	FROM TB_HOLIDAYS H JOIN TB_COMM_CD C                                       		\r\n");
            sql.append("            			ON H.HOLIDAY_TP = C.CD                                             		\r\n");
            sql.append("            			AND C.GROUP_CD = '108'                                             		\r\n");
            
            if (!year.equals(CodeMetrix.ALL)) {
	            sql.append("            			AND H.HOLIDAY_DT                                                   		\r\n");
	            sql.append("							BETWEEN                                                                                   					\r\n");
				
	            sql.append("										CONVERT(DATETIME , ('"+year+"' + REPLACE('"+month+"' , 'ALL' , '01') + '01') )					\r\n");
	            sql.append("							AND                                                                                       					\r\n");
	            sql.append("										CASE  WHEN '"+ month +"' = 'ALL'                                              					\r\n");
	            sql.append("										THEN DBO.LASTDAYOFMONTH(CONVERT(DATETIME	,  '" + year + "' + '12' + '31')) 					\r\n");
	            sql.append("										ELSE DBO.LASTDAYOFMONTH(CONVERT(DATETIME	, ('" + year + "' + REPLACE('"+month+"' , 'ALL' , '01') + '01')))					\r\n");
	            sql.append("										END                                                                           					\r\n");
            }
            
			sql.append("			)   Z                                                                               \r\n");
			System.out.println("======================================\r\n");
			System.out.println("쿼리실행 내용\r\n" + sql.toString());
			System.out.println("======================================\r\n");

			pstmt = con.prepareStatement(sql.toString());
			
			/************** PARAMETERS START ****************************/
			/************** PARAMETERS E N D ****************************/
			
			rs = pstmt.executeQuery();

			if (rs.next()) {
				rowCount = rs.getInt("ROWCNT");
			}

		} catch (SQLException se) {
			se.printStackTrace();
			BaseException dex = new BaseException();
			dex.setException(se);
			dex.setAppReturnCode("10");
			throw dex;
		} catch (Exception e) {
			BaseException dex = new BaseException();
			dex.setException(e);
			dex.setAppReturnCode("20");
			throw dex;
		} finally {
			try {
				if (pstmt != null)
					pstmt.close();
				if (rs != null)
					rs.close();
				if (con != null)
					con.close();
			} catch (SQLException fse) {
				BaseException dex = new BaseException();
				dex.setException(fse);
				dex.setAppReturnCode("10");
				throw dex;
			}
		}
	}
	
	/**
	 * <pre>
	 * 휴일목록 조회
	 * </pre>
	 * @param year		--		연도
	 * @param month		--		월
	 * @param s_row		-		부터 row
	 * @param e_row		-		까지 row
	 * @throws BaseException
	 * @since	200/02/12
	 * @author Yang	D
	 */
	public void retrieveAjaxList(String year , String month , int s_row , int e_row) throws BaseException {
		
		System.out.println(year);
		System.out.println(month);

		try {

			con = getDataSource().getConnection(); //근태관리 Connection

			sql.setLength(0);
			
            sql.append("            SELECT  Z.*                                                                    		\r\n");
            sql.append("            FROM                                                                           		\r\n");
            sql.append("            (                                                                              		\r\n");
            sql.append("            	SELECT  ROW_NUMBER() OVER(ORDER BY HOLIDAY_DT) ROWNUM                      		\r\n");
            sql.append("            		,   H.HOLIDAYS_SEQ                                                     		\r\n");
            sql.append("            		,   CONVERT(VARCHAR , H.HOLIDAY_DT , 112)       DATE                   		\r\n");
            sql.append("            		,   H.HOLIDAY_TP                                TYPE_CD                		\r\n");
            sql.append("            		,   C.CD_NM                                     TYPE_NM                		\r\n");
            sql.append("            		,   H.HOLIDAY_NM                                                       		\r\n");
            sql.append("            	FROM TB_HOLIDAYS H JOIN TB_COMM_CD C                                       		\r\n");
            sql.append("            			ON H.HOLIDAY_TP = C.CD                                             		\r\n");
            sql.append("            			AND C.GROUP_CD = '108'                                             		\r\n");

            if (!year.equals(CodeMetrix.ALL)) {
	            sql.append("            			AND H.HOLIDAY_DT                                                   		\r\n");
	            sql.append("							BETWEEN                                                                                   					\r\n");
				
	            sql.append("										CONVERT(DATETIME , ('"+year+"' + REPLACE('"+month+"' , 'ALL' , '01') + '01') )					\r\n");
	            sql.append("							AND                                                                                       					\r\n");
	            sql.append("										CASE  WHEN '"+ month +"' = 'ALL'                                              					\r\n");
	            sql.append("										THEN DBO.LASTDAYOFMONTH(CONVERT(DATETIME	,  '" + year + "' + '12' + '31')) 					\r\n");
	            sql.append("										ELSE DBO.LASTDAYOFMONTH(CONVERT(DATETIME	, ('" + year + "' + REPLACE('"+month+"' , 'ALL' , '01') + '01')))					\r\n");
	            sql.append("										END                                                                           					\r\n");
            }
            
			sql.append("			)   Z                                                                               \r\n");
			sql.append("			WHERE Z.ROWNUM	BETWEEN		?	AND		?                                           \r\n");            
            
			System.out.println("======================================\r\n");
			System.out.println("휴일목록 조회 내용\r\n" + sql.toString());
			System.out.println("======================================\r\n");

			pstmt = con.prepareStatement(sql.toString());
			
			/************** PARAMETERS START ****************************/

			int idx = 1;

			pstmt.setInt(idx++			, 		s_row); 	//
			pstmt.setInt(idx++			, 		e_row); 	//

			/************** PARAMETERS E N D ****************************/


			
			rs = pstmt.executeQuery();

			list = new ArrayList<HashMap<String, String>>();

			while (rs.next()) {
				//				각 행의 각각컬럼의 데이터를 담는다.
				HashMap<String, String> hash_map = new HashMap<String, String>();

				//hash_map.put("대소문자로 구분하여 사용할 것." , "디비에서는 상관없음.");
				//jsp에서 <s:property value='upper_dept_no'/> 와 같아야 함.
				hash_map.put("seq"			, 	String.valueOf(rs.getLong("HOLIDAYS_SEQ"))); 
				hash_map.put("date"			, 	rs.getString("DATE"));
				hash_map.put("type_cd"		, 	rs.getString("TYPE_CD"));
				hash_map.put("type_nm"		, 	rs.getString("TYPE_NM"));
				hash_map.put("holiday_nm"	, 	rs.getString("HOLIDAY_NM"));

				list.add(hash_map); // 한행의 데이터인 맵객체를 담는다.
			}

		} catch (SQLException se) {
			se.printStackTrace();
			BaseException dex = new BaseException();
			dex.setException(se);
			dex.setAppReturnCode("10");
			throw dex;
		} catch (Exception e) {
			BaseException dex = new BaseException();
			dex.setException(e);
			dex.setAppReturnCode("20");
			throw dex;
		} finally {
			try {
				if (pstmt != null)
					pstmt.close();
				if (rs != null)
					rs.close();
				if (con != null)
					con.close();
			} catch (SQLException fse) {
				BaseException dex = new BaseException();
				dex.setException(fse);
				dex.setAppReturnCode("10");
				throw dex;
			}
		}
	}

	/**
	 * <pre>휴일등록/스정 저장</pre>
	 * @param date			-	휴일일자
	 * @param type			-	휴일코드
	 * @param etc			-	휴일명
	 * @param seq			-	스퀀스
	 * @throws BaseException
	 */
	public void saveHolidays(String date , String type , String etc , int seq) throws BaseException {
		
		try {

			con = getDataSource().getConnection(); //근태관리 Connection

			sql.setLength(0);
			
			if (crud.equals(CodeMetrix.INSERT)) {
				
				sql.append("		INSERT INTO TB_HOLIDAYS (		HOLIDAY_DT		,		HOLIDAY_TP	,	HOLIDAY_NM	)			\r\n");
				sql.append("		     VALUES				(	CONVERT(DATETIME , ?	)	,	?		,		?		)			\r\n");				
			
			} else if (crud.equals(CodeMetrix.UPDATE)) {
			
				sql.append("			UPDATE                   											\r\n");
				sql.append("			        TB_HOLIDAYS      											\r\n");
				sql.append("			SET                      											\r\n");
				sql.append("			        HOLIDAY_DT  	=   CONVERT(DATETIME , ?	)				\r\n");
				sql.append("			    ,   HOLIDAY_TP  	=   ?										\r\n");
				sql.append("			    ,   HOLIDAY_NM  	=   ?										\r\n");
				sql.append("			 WHERE	HOLIDAYS_SEQ	=   " + seq + "										\r\n");
			
			}

			System.out.println("======================================\r\n");
			System.out.println("쿼리실행 내용\r\n" + sql.toString());
			System.out.println("======================================\r\n");

			pstmt = con.prepareStatement(sql.toString());
			
			int idx	=	1;
			
			pstmt.setString(idx++ 	, 	date);
			pstmt.setString(idx++	, 	type);
			pstmt.setString(idx++	, 	etc);
			
			int row	=	pstmt.executeUpdate();

			if (row > 0) {
				msg	=	CodeMetrix.OK;
			}

		} catch (SQLException se) {
			se.printStackTrace();
			BaseException dex = new BaseException();
			dex.setException(se);
			dex.setAppReturnCode("10");
			throw dex;
		} catch (Exception e) {
			BaseException dex = new BaseException();
			dex.setException(e);
			dex.setAppReturnCode("20");
			throw dex;
		} finally {
			try {
				if (pstmt != null)
					pstmt.close();
				if (rs != null)
					rs.close();
				if (con != null)
					con.close();
			} catch (SQLException fse) {
				BaseException dex = new BaseException();
				dex.setException(fse);
				dex.setAppReturnCode("10");
				throw dex;
			}
		}
	}
	
	/**
	 * <pre>휴일삭제</pre>
	 * @param	seq	-	스퀀스
	 * @throws BaseException
	 */
	public void deleteHolidaysBySeq(String seq) throws BaseException {
		
		try {

			con = getDataSource().getConnection(); //근태관리 Connection

			sql.setLength(0);

            sql.append("				DELETE FROM		TB_HOLIDAYS           								\r\n");
            sql.append("				WHERE			HOLIDAYS_SEQ IN ( #1 )								\r\n");
            
            String query = sql.toString();
            //치환
            query 		= query.replace("#1"	,	seq);
            

			System.out.println("======================================\r\n");
			System.out.println("쿼리실행 내용\r\n" + query);
			System.out.println("======================================\r\n");

			pstmt = con.prepareStatement(query);

			int row = pstmt.executeUpdate();

			if (row > 0) {
				msg = CodeMetrix.OK;
			}

		} catch (SQLException se) {
			se.printStackTrace();
			BaseException dex = new BaseException();
			dex.setException(se);
			dex.setAppReturnCode("10");
			throw dex;
		} catch (Exception e) {
			BaseException dex = new BaseException();
			dex.setException(e);
			dex.setAppReturnCode("20");
			throw dex;
		} finally {
			try {
				if (pstmt != null)
					pstmt.close();
				if (rs != null)
					rs.close();
				if (con != null)
					con.close();
			} catch (SQLException fse) {
				BaseException dex = new BaseException();
				dex.setException(fse);
				dex.setAppReturnCode("10");
				throw dex;
			}
		}

	}
	
	/**
	 * <pre>Sequence로 데이터를 조회한다.</pre>
	 * @param seq
	 * @throws BaseException
	 */
	public void retrieveHolidaysBySeq(int seq) throws BaseException {
		
		try {

			con = getDataSource().getConnection(); //근태관리 Connection

			sql.setLength(0);

            sql.append("					SELECT                                                  														\r\n");
            sql.append("							HOLIDAYS_SEQ, REPLACE(CONVERT(VARCHAR(10) , HOLIDAY_DT , 111) , '/' , '-') HOLIDAY_DT					\r\n");
            sql.append("					        , HOLIDAY_TP, HOLIDAY_NM                                                    							\r\n");
            sql.append("					FROM                                                    														\r\n");
            sql.append("							TB_HOLIDAYS                                     														\r\n");
            sql.append("					WHERE                                                   														\r\n");
            sql.append("							HOLIDAYS_SEQ	=	?                           														\r\n");


			System.out.println("======================================\r\n");
			System.out.println("쿼리실행 내용\r\n" + sql.toString());
			System.out.println("======================================\r\n");

			pstmt 	= 	con.prepareStatement(sql.toString());
			
			/************** PARAMETERS START ****************************/

			int idx = 1;

			pstmt.setInt(idx++, seq); //sequence

			/************** PARAMETERS E N D ****************************/


			rs		=	pstmt.executeQuery();
			
			list 	= 	new ArrayList<HashMap<String, String>>();

			if (rs.next()) {
				//				각 행의 각각컬럼의 데이터를 담는다.
				HashMap<String, String> hash_map = new HashMap<String, String>();

				//hash_map.put("대소문자로 구분하여 사용할 것." , "디비에서는 상관없음.");
				//jsp에서 <s:property value='upper_dept_no'/> 와 같아야 함.
				hash_map.put("seq"			, 	String.valueOf(rs.getLong("HOLIDAYS_SEQ"))); 
				hash_map.put("date"			, 	rs.getString("HOLIDAY_DT"));
				hash_map.put("type_cd"		, 	rs.getString("HOLIDAY_TP"));
				hash_map.put("type_nm"		, 	rs.getString("HOLIDAY_NM"));
				
				list.add(hash_map); // 한행의 데이터인 맵객체를 담는다.
			}


		} catch (SQLException se) {
			se.printStackTrace();
			BaseException dex = new BaseException();
			dex.setException(se);
			dex.setAppReturnCode("10");
			throw dex;
		} catch (Exception e) {
			BaseException dex = new BaseException();
			dex.setException(e);
			dex.setAppReturnCode("20");
			throw dex;
		} finally {
			try {
				if (pstmt != null)
					pstmt.close();
				if (rs != null)
					rs.close();
				if (con != null)
					con.close();
			} catch (SQLException fse) {
				BaseException dex = new BaseException();
				dex.setException(fse);
				dex.setAppReturnCode("10");
				throw dex;
			}
		}
		
	}


	/*************************************************************************************************/
	public List<HashMap<String, String>> getList() {
		return list;
	}


	public void setList(List<HashMap<String, String>> list) {
		this.list = list;
	}


	public String getMsg() {
		return msg;
	}


	public void setMsg(String msg) {
		this.msg = msg;
	}


	public Object getCrud() {
		return crud;
	}


	public void setCrud(Object crud) {
		this.crud = crud;
	}


	public int getRowCount() {
		return rowCount;
	}


	public void setRowCount(int rowCount) {
		this.rowCount = rowCount;
	}


	/*************************************************************************************************/
}
